package com.augurit.gzcgwsp.state.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.springside.modules.orm.Page;

import com.augurit.common.base.dao.BaseDao;

/**
 * 统计dao基类
 * @author zhangjun
 *
 */
public class StateBaseDao extends BaseDao{
	private static final Logger log = Logger.getLogger(StateBaseDao.class);
	
	private static String path = "";
	/**
	 * 分页查询
	 * @param page
	 * @param map
	 * @return
	 */
	public List searchByPage(String sql,Page page, Map map) {
		StringBuffer sb=new StringBuffer();
	    sb.append("  select * from ("+"\r");
	    sb.append("  select  *   from ("+"\r");
	    sb.append(" select  a.*,rownum num from ("+"\r");
	    sb.append(sql+") a )"+"\r");
	    sb.append(" where   rownum <="+page.getPageSize()*page.getPageNo()+")"+"\r");
	    sb.append(" where   num> ="+((page.getPageNo()-1)*page.getPageSize()+1)+"\r");
		List list= this.queryForList(sb.toString());
		return list;
	}
	/**
	 * 分页查询
	 * @param page
	 * @param map
	 * @return
	 */
	public Page findPage(String sql,Page page,Map map) {
		String sizesql=" select count(*) as countnum from ( " + sql+" )";
		List list=this.queryForList(sizesql);
		Map countMap=(Map) list.get(0);
		String total=(String) countMap.get("countnum");
		List datas=searchByPage(sql, page, map);
		if(StringUtils.isNotBlank(total)){
			page.setTotalCount(Long.valueOf(total));
		}else{
			page.setTotalCount(0);
		}
		page.setResult(datas);
		return page;
	}
	
	/**
	 * sql修改某张表
	 * @param sql
	 * @return
	 */
	public  int updateDateBase(String sql) {
		logSQLBefor(sql);
		Connection conn = null;
		Statement st = null;
		int num = 0;
		try {
			long timeBefore = System.currentTimeMillis();
			conn = getConnection();
			st = getStatement(conn);
			num = st.executeUpdate(sql);
			logSQLAfter(sql,  timeBefore);
		}catch(Exception e) {
			log.error("更错误", e);
		}finally {
			free(conn, st, null);
		}
		
		return num;
	}
	/**
	 * 简单SQL查询  
	 * 
	 * @param sql
	 * @return
	 */
	public  List<Map> queryForList(String sql) {
		logSQLBefor(sql);
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List<Map> tempList = new ArrayList<Map>();
		try {
			long timeBefore = System.currentTimeMillis();
			conn = getConnection();
			st = getStatement(conn);
			rs = st.executeQuery(sql);
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount();
			while(rs.next()) {
				Map<String, String> map = new LinkedHashMap<String, String>();
				for(int i=1; i<=columnCount; i++) {
					String colName = meta.getColumnLabel(i).toLowerCase();
					String colValue = rs.getString(i);
					map.put(colName, colValue);
				}
				tempList.add(map);
			}
			
			logSQLAfter(sql,  timeBefore);
		}catch(Exception e) {
			log.error("查找异常", e);
		}finally {
			free(conn, st, rs);
		}
		
		return tempList;
	}
	
	/*
	 * 获取Statement，并且设置最长的查询时间，超过该时间会自动抛出异常
	 */
	private  Statement getStatement(Connection conn) throws Exception {
		Statement t = conn.createStatement();
		return t;
	}
	/**  
	 * 关闭连接资源  
	 * @param conn  
	 */
	public static void free(Connection conn, Statement st, ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/*
	 * 日志记录SQL执行前的信息
	 */
	public static void logSQLBefor(String sql) {
		StackTraceElement[] ste = new Throwable().getStackTrace();
		StringBuffer CallStack = new StringBuffer();
		for (int i = 0; i < ste.length; i++) {
			CallStack.append(ste[i].toString() + " | ");
			if (i == 6)
				break;
		}
		ste = null;
		path = CallStack.toString();
//		log.info("执行路径：" + path);
//		log.info("执行脚本：" + sql);
	}
	
	
	@SuppressWarnings("deprecation")
	public  Connection getConnection() {
		Connection conn = null;
		try {
			conn=getSession().connection();
		} catch (HibernateException e) {
			log.error("查询统计数据连接错误"+e);
		}
		
		return conn;
	}
	
	
	/*
	 * 日志记录SQL执行后的信息
	 */
	public static void logSQLAfter(String sql,  long timeBefore) {
		long timeDiff = System.currentTimeMillis() - timeBefore;
		String timeDiffResult = timeDiff < 1000 ? timeDiff + "毫秒!" : (timeDiff / 1000) + "秒钟!";
//		log.info("执行时间：" + timeDiffResult);
	}
	
}
